﻿using EazyCMS.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace EazyCMS.BLL
{
   public class common_data_tools_dal
    {
        //得到学年的方法
        public string getxueniandal()
        {
            string sql = "select * from dt_xuenian";
            DataTable dt = DbHelperSQL.ExecuteDataSet(sql);
            string strs = dt.Rows[0]["xq"].ToString() + "," + dt.Rows[0]["strstart"].ToString() + "," + dt.Rows[0]["xn"].ToString();
            return strs;
        }
        //学年的修改方法
        public int insertxueniandal(string xq,string kssj ,string xn )
        {
            string sql = "update dt_xuenian set xq='"+xq+"',strstart='"+kssj+"',xn='"+xn+"' where id=1";
            return  DbHelperSQL.ExecuteSql(sql);
        }
       // 根据用户名统计文章数
        public DataTable get_count_article(string author,DateTime kssj,DateTime jssj)
        {
          return  DbHelperSQL.RunProcedure("getarticlecount", new IDataParameter[] { new SqlParameter("@author",author),new SqlParameter("@kssj",kssj),new SqlParameter("@jssj",jssj) }, "mytable").Tables[0];
        }
        //根据用户组得到用户数
        public DataTable get_user_bygroups(string group_id)
        {
            string sql = "select user_name from  dbo.dt_users where group_id="+group_id;
         return   DbHelperSQL.ExecuteDataSet(sql);
            
        }
        //稿件统计开始日期
        public int update_total_count(string kssj)
        {
            string sql = "update dbo.dt_count_contribute set starttime='"+kssj+"' where id=1";
            return DbHelperSQL.ExecuteSql(sql);
        }
        //得到稿件统计的开始时间
        public DateTime get_total_time()
        {
            string sql = "select starttime from dbo.dt_count_contribute where id=1";
            return DateTime.Parse(DbHelperSQL.GetSingle(sql).ToString());
        }
        //删除用户发表的文章
        public int del_article(int id)
        {
            string sql = "delete from dt_article where id="+id;
            string sql2 = "delete from dt_article_attribute_value where article_id="+id;
            int i = DbHelperSQL.ExecuteSql(sql2) + DbHelperSQL.ExecuteSql(sql);
            return i;
        }
        /// <summary>
        /// 图片新增is_special 1为首页焦点图   2为专题焦点图
        /// </summary>
        /// <param name="url"></param>
        /// <param name="is_special"></param>
        /// <returns></returns>
        public int add_phone(string url,int is_special)
        {
            string sql = "insert into dt_img_manager (imgurl,is_special) values('"+url+"',"+is_special+")";
          return  DbHelperSQL.ExecuteSql(sql);
        }

        /// <summary>
        /// 图片新增is_special 1为首页焦点图   2为专题焦点图
        /// </summary>
        /// <param name="url"></param>
        /// <param name="is_special"></param>
        /// <returns></returns>
        public DataTable get_phone()
        {
            string sql = "select * from dt_img_manager order by id";
            return DbHelperSQL.ExecuteDataSet(sql);
        }
        public bool Delete(int id,string url_img)
        {
            string sql = "delete from dt_img_manager where id="+id;
            int i=DbHelperSQL.ExecuteSql(sql);
          
            if (i > 0)
            { return true; }
            else
            {
                return false;
            }
        }
        public DataTable get_focus_img(string special_id, int top)
        {
            string sql = "select top "+top.ToString()+ " * from dt_img_manager where is_special=" + special_id;
            return DbHelperSQL.ExecuteDataSet(sql);

        }
        public DataTable getyonghu(int id)
        {
            string sql = "select id,user_name from dt_users where group_id="+id;
          return  DbHelperSQL.ExecuteDataSet(sql);
        }
        public DataTable getxuequ()
        {
            string sql = "select id,title from dt_user_groups where id=2 or id=3 or id=4 or id=5 or id=7";
            return DbHelperSQL.ExecuteDataSet(sql);
        }
        public string select_img_list(string id)
        {
            string sql = "select img_url_list from dt_article where id="+id;
            return  DbHelperSQL.ExecuteDataSet(sql).Rows[0]["img_url_list"].ToString();
        }
        ///广告位显示隐藏
        public void updatabarvalue(string ss)
        {
            string sql = "update dbo.dt_bar_value set zhi='"+ ss + "' where id=1";
            DbHelperSQL.ExecuteSql(sql);
        }

        //广告位值的查询
        public string selectbarvalue()
        {
            string sql = "select zhi from  dt_bar_value where id=1";
          return  DbHelperSQL.GetSingle(sql).ToString();
        }
        /// 更新总访问数和 当天的访问数
        public void updatenum()
        {
            //首先读取数据库中的时间
            string sql = "select todate from dt_all_num where id=1";
            //得到当前时间
            int year = DateTime.Now.Year;
            int month = DateTime.Now.Month;
            int day = DateTime.Now.Day;
            DateTime jintstar = Convert.ToDateTime(year + "-" + month + "-" + day + " 00:00:01");
            DateTime jintend = Convert.ToDateTime(year + "-" + month + "-" + day + " 23:59:59");
            DateTime realtime = Convert.ToDateTime(DbHelperSQL.GetSingle(sql));
            if (realtime > jintstar && realtime < jintend)
            {
                //当天
                string sqlupdateone = "update dbo.dt_all_num set total_num=total_num+1,todayl_num=todayl_num+1 where id=1";
                DbHelperSQL.ExecuteSql(sqlupdateone);
            }
            else
            {
                DateTime currenttime = DateTime.Now;
                //不在当天范围内
                string sqlupdate = "update dbo.dt_all_num set total_num=total_num+1,todayl_num=1,todate='"+currenttime+"' where id=1";
                DbHelperSQL.ExecuteSql(sqlupdate);
               
            }


        }
        ///读取访问总数  今天总数
        public string get_tongji()
        {
            string sql = "select total_num,todayl_num from dbo.dt_all_num where id=1 ";
            DataTable dt = DbHelperSQL.ExecuteDataSet(sql);
            return dt.Rows[0]["total_num"].ToString() + "," + dt.Rows[0]["todayl_num"].ToString();

        }
        //总文章数和总点击率
        public string GetTotalArticle()
        {
            string sql = "select COUNT(title) as zwzs,SUM(click) as zdjl from dbo.dt_article";
            DataTable dt = DbHelperSQL.ExecuteDataSet(sql);
            return dt.Rows[0]["zwzs"].ToString() + "," + dt.Rows[0]["zdjl"].ToString();

        }
        //今日点击率 和今日文章发表数量
        public string GetTodayContribute(string k,string j)
        {
            string sql = "select COUNT(title) todayzwzs,SUM(click) as todayzdjl from dbo.dt_article where status=0 and  (add_time between '"+k+"' and '"+j+"')";
            DataTable dt = DbHelperSQL.ExecuteDataSet(sql);
            return dt.Rows[0]["todayzwzs"].ToString() + "," + dt.Rows[0]["todayzdjl"].ToString();
        }
        ///根据文章id得到文章内容
        public string getarticleinfo(string id)
        {
            string sql = "select content from dbo.dt_article where id = '"+id+"'";
          return  DbHelperSQL.GetSingle(sql).ToString();
        }
        //得到频道信息
        public DataTable GetPinDao()
        {
            string sql = "select * from dt_channel where is_display=1";
            return DbHelperSQL.ExecuteDataSet(sql);
        }
    }
}
